package org.zjut.db;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.zjut.bean.UserInfo;
import org.zjut.util.CommonUtil;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * @date 2017-08-10 22:01
 * @author Rising
 * @des 账户相关操作
 */
public class AccountDataBase extends JdbcDaoSupport {
    String dataBase = "pso_recommond.";
    String tb_account = "account";
    String tb_user = "user";


    /*===================账户操作=======================================*/

    /**
     * 插入用户信息
     *
     * @return
     */
    @Transactional(propagation= Propagation.REQUIRED)
    public Map<String, Object> insertUserAccount(Map<String, Object> params) throws Exception{
        String account, password, userName, userAge, userRole, userSex,userEmail;
        Map<String, Object> result = new HashMap<>();
        //参数处理
        try {
            account = params.get("account").toString();
            password = params.get("password").toString();
            userName = params.get("userName").toString();
            userAge = params.get("userAge").toString();
            userRole = params.get("userRole").toString();
            userSex = params.get("userSex").toString();
            userEmail = params.get("userEmail").toString();

        } catch (Exception e) {
            System.out.println(e.getMessage() + "");
            result.put("success", false);
            result.put("error", "请检查参数");
            return result;
        }

        //先检查账户是否已经存在
        try {
            String preSql = "SELECT * FROM "+dataBase+ tb_account +" as a WHERE a.account = '"+account+"'";
            System.out.println(preSql);
            List temp = getJdbcTemplate().queryForList(preSql);
            if(temp!=null&&!temp.isEmpty()){
                return CommonUtil.setErrorInfo("已经存在该用户");
            }
        }catch (Exception e){
            System.out.println(e.getMessage()+"");
            return CommonUtil.setErrorInfo("数据库查询失败");
        }
        //先插入account 表，再插入user_info
        String sql = "INSERT INTO " + dataBase + tb_account +" (code,account,password) VALUES (?,?,?)";
        String sql2 = "INSERT INTO " + dataBase + tb_user+"(code,accountCode,name,sex,age,role,email) VALUES(?,?,?,?,?,?,?)";

        //生成唯一编码CODE
        String userCode = UUID.randomUUID().toString();
        String userInfoCode = UUID.randomUUID().toString();
        try {
            getJdbcTemplate().update(sql, new Object[]{userCode, account, password});
            getJdbcTemplate().update(sql2, new Object[]{userInfoCode, userCode, userName, userSex, userAge, userRole,userEmail});
            UserInfo userInfo = new UserInfo();
            userInfo.setUserCode(userCode);
            userInfo.setUserName(userName);
            userInfo.setUserAge(Integer.valueOf(userAge));
            userInfo.setUserSex(Integer.valueOf(userSex));
            Map<String, Object> message = new HashMap<>();
            message.put("userInfo", userInfo);
            result.put("success", true);
            result.put("message", message);
        } catch (Exception e) {
            System.out.print(e.getMessage() + "");
            throw e;
        }
        return result;
    }

    /**
     * 登录服务
     * @param user
     * @return
     */
    public Map<String,Object> login(Map<String,Object> user){
        String account , password;
        Map<String,Object> result = new HashMap<>();
        try{
            account = user.get("account").toString();
            password = user.get("password").toString();
        }catch (Exception e){
            System.out.println(e.getMessage()+"");
            return CommonUtil.setErrorInfo("参数有问题，请检查");
        }

        //查询是否有这个用户，并返回用户信息
        String sql = "select * from "+dataBase+"view_user_info as a " +
                "where a.account = ? and a.password = ? ";
        try{
            List<Map<String,Object>> userInfo = getJdbcTemplate().queryForList(sql,new Object[]{account,password});
            if(userInfo!=null && !userInfo.isEmpty()){
                Map<String,Object> message = new HashMap<>();
                message.put("userInfo",userInfo.get(0));
                result.put("success",true);
                result.put("message",message);
            }else{
                result = CommonUtil.setErrorInfo("用户名或密码错误");
            }
            return result;
        }catch (Exception e){
            System.out.print(e.getMessage()+"");
            return CommonUtil.setErrorInfo("查询失败");
        }
    }

    /**
     * 获取账户信息
     * @param params
     * @return
     */
    public Map<String,Object> getAccount(Map<String,Object> params) {
        String userCode="",userName="",account="",type;
        String userRole=null;
        Map<String,Object> result = new HashMap<>();
        try{
            type = params.get("type").toString();
            if(type.equals("0")){
                userCode = params.get("userCode").toString();
            }else if(type.equals("1")){
                userName = params.get("userName").toString();
            }else if(type.equals("2")){
                account = params.get("account").toString();
            }
            if(params.containsKey("userRole")){
                userRole = params.get("userRole").toString();
            }
        }catch (Exception e){
            System.out.println(e.getMessage()+"");
            return CommonUtil.setErrorInfo("参数有问题，请检查");
        }

        String sql = "SELECT * FROM "+dataBase+"view_user_info as a WHERE 1=1 ";

        if(type.equals("0")){
            sql = sql + " AND a.userCode = '"+userCode+"'";
        }else if (type.equals("1")){
            sql = sql + " AND a.userName = '"+userName+"'";
        }else if(type.equals("2")){
            sql = sql + " AND a.account = '"+account+"'";
        }

        if(userRole!=null){
            sql = sql + " AND a.userRole = "+userRole + " ";
        }

        System.out.println(sql);
        try{
            List<Map<String,Object>> userInfos = getJdbcTemplate().queryForList(sql);
            Map<String,Object> message = new HashMap<>();
            message.put("userInfo",userInfos);
            result.put("success",true);
            result.put("message",message);
        }catch (Exception e){
            System.out.println(e.getMessage()+"");
            return CommonUtil.setErrorInfo("查询失败");
        }

        return result;
    }

    /**
     * 修改用户信息
     * @param params
     * @return
     */
    public Map<String,Object> editAccount(Map<String,Object> params){
        String userCode, userName, userAge, userSex,userEmail;
        Map<String, Object> result = new HashMap<>();
        //参数处理
        try {
            userCode = params.get("userCode").toString();
            userName = params.get("userName").toString();
            userAge = params.get("userAge").toString();
            userSex = params.get("userSex").toString();
            userEmail = params.get("userEmail").toString();
        } catch (Exception e) {
            System.out.println(e.getMessage() + "");
            result.put("success", false);
            result.put("error", "请检查参数");
            return result;
        }

        String sql = "UPDATE "+dataBase+tb_user+" SET name = ? , age = ? , sex = ? ,email = ? " +
                "WHERE code = ?";
        System.out.println(sql);

        try{
            int row = getJdbcTemplate().update(sql,new Object[]{userName,userAge,userSex,userEmail,userCode});
            if(row > 0 ){
                HashMap<String,Object> param = new HashMap<>();
                param.put("userCode",userCode);
                param.put("type",0);

                result = getAccount(param);
            }else{
                result.put("success",false);
                result.put("message","没有需要修改的数据");
            }
        }catch (Exception e){
            System.out.println(e.getMessage()+"");
            return CommonUtil.setErrorInfo("修改失败");
        }
        return result;
    }

    /**
     * 删除账户及相关信息
     * @param accountCode
     * @return
     */
    @Transactional(propagation= Propagation.REQUIRED)
    public Map<String,Object> delAccount(String accountCode){
        Map<String,Object> result = new HashMap<>();
        //user 以及关联表 user_info 记录都需要删除
        String sql = "DELETE FROM "+dataBase+tb_user+" WHERE accountCode = '"+ accountCode +"'";
        String sql2 = "DELETE FROM " +dataBase+tb_account+" WHERE code = '"+ accountCode +"'";

        try{
            getJdbcTemplate().update(sql);
            getJdbcTemplate().update(sql2);
            result.put("success",true);
            result.put("message","删除成功");
        }catch (Exception e){
            System.out.println(e.getMessage()+"");
            return CommonUtil.setErrorInfo("删除失败");
        }
        return result;
    }
}
